<?php
class Publicacion extends Doctrine_Record {
    public function setTableDefinition() {
        $this->setTableName('publicacion');
        $this->hasColumn('id', 'integer', 5, array('primary' => true, 'unsigned'=>true, 'autoincrement'=>true));
        $this->hasColumn('titulo', 'string', 255);
        $this->hasColumn('contenido', 'string', 10000);
        $this->hasColumn('thumb', 'string', 255);
        $this->hasColumn('publicado', 'timestamp');
        $this->hasColumn('id_categoria', 'integer', 3, array('unsigned'=>true));
        $this->hasColumn('id_estado', 'integer', 1, array('unsigned'=>true));
    }
    
    public function setUp(){
        $this->hasOne('Categoria as categoria',array(
            'local'=>'id_categoria',
            'foreign'=>'id'
        ));
        $this->hasOne('Estado as estado',array(
            'local'=>'id_estado',
            'foreign'=>'id'
        ));
        $this->hasMany('Imagen as imagenes', array(
            'local' => 'id',
            'foreign' => 'id_publicacion'
        ));
        $this->hasMany('Visita as visitas', array(
            'local' => 'id',
            'foreign' => 'id_publicacion'
        ));
        $this->hasMany('Tag as tags', array(
            'local' => 'id_publicacion',
            'foreign' => 'id_tag',
            'refClass' => 'Tagging'
        ));
        $this->hasMany('Publicidad as publicidades', array(
            'local' => 'id_publicacion',
            'foreign' => 'id_publicidad',
            'refClass' => 'RelPublicidadPublicacion'
        ));
        //behaviors
        $this->actAs('Sluggable', array('fields'=>array('titulo'),'unique'=>true,'canUpdate'=>true,'name'=>'slug'));
        $this->actAs('Timestampable');
    }
    
    public function getPortada () {
        return Doctrine_Query::create()
                ->select('i.*')
                ->from('Imagen i')
                ->innerJoin('i.publicacion p WITH p.id = '.$this->_get('id'))
                ->orderBy('i.orden')
                ->limit(1)
                ->offset(0)
                ->fetchOne()
        ;
    }


    public function sortedImages ($hydrateMode=Doctrine::HYDRATE_ARRAY) {
        return Doctrine_Query::create()
                ->select('i.*')
                ->from('Imagen i')
                ->innerJoin('i.publicacion p WITH p.id = '.$this->id)
                ->orderBy('i.orden')
                ->execute(array(), $hydrateMode)
        ;
    }
    
    public function tagsLink () {
        $q = Doctrine_Query::create()
                ->select('concat("<li><a href=\"tags/",t.slug,"\">",t.value,"</a></li>")')
                ->from('Tag t')
                ->innerJoin('t.publicaciones p WITH p.id = '.$this->id)
                ->groupBy('t.id');
        $tags = $q->execute(array(), Doctrine::HYDRATE_SINGLE_SCALAR);
        if (count($tags) == 1) {
            $result = $tags;
        } else if (count($tags) > 1) {
            $result = implode('', $tags);
        } else {
            $result = '';
        }
        return $result;
    }
    
    public function tagsToString ($separator = ', ') {
        $q = Doctrine_Query::create()
                ->select("group_concat(t.value separator '$separator') as tags")
                ->from('Tag t')
                ->innerJoin('t.publicaciones p WITH p.id = '.$this->id)
                ->groupBy('p.id');
        $tags = $q->fetchOne();
        return ($tags)?$tags->tags:'';
    }
    
    public static function search($keywords) {
        $ids = strstr($keywords, ' ') ? self::multiple_search($keywords) : self::simple_seach($keywords);
        return $ids;
    }
    
    public static function simple_seach ($keyword) {
        $q = Doctrine_Query::create()
                ->select("group_concat(p.id separator ',') as ids")
                ->from('Publicacion p')
                ->where('p.titulo like ?', "%$keyword%")
                ->andWhere('p.id_estado = ?', Estado::VISIBLE)
                ->orderBy('p.created_at desc');
        $result = $q->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
        $ids = ($result['ids'])?explode(',', $result['ids']):array(0);
        $q2 = Doctrine_Query::create()
                ->select("group_concat(p.id separator ',') as ids")
                ->from('Publicacion p')
                ->where('p.contenido like ?', "%$keyword%")
                ->andWhere('p.id_estado = ?', Estado::VISIBLE)
                ->andWhereNotIn('p.id', $ids)
                ->orderBy('p.created_at desc');
        $result = $q2->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
        if(($result['ids'])) $ids = array_merge($ids, explode(',', $result['ids']));
        return array_unique($ids);
    }
    
    public static function multiple_search ($keywords) {
        $andLike = 'like "%'.str_replace(' ', '%" and colum like "%', $keywords).'%"';
        $orLike = 'like "%'.str_replace(' ', '%" or colum like "%', $keywords).'%"';
        
        //busqueda en titulo con and en el where
        $serchTerm = str_replace('colum', 'p.titulo', $andLike);
        $q = Doctrine_Query::create()
                ->select("group_concat(p.id separator ',') as ids")
                ->from('Publicacion p')
                ->where('p.titulo '.$serchTerm)
                ->andWhere('p.id_estado = ?', Estado::VISIBLE)
                ->orderBy('p.created_at desc');
        $result = $q->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
        $ids = ($result['ids'])?explode(',', $result['ids']):array(0);
        
        //busqueda en contenido con and en el where
        $serchTerm = str_replace('colum', 'p.contenido', $andLike);
        $q2 = Doctrine_Query::create()
                ->select("group_concat(p.id separator ',') as ids")
                ->from('Publicacion p')
                ->where('p.contenido '.$serchTerm)
                ->andWhere('p.id_estado = ?', Estado::VISIBLE)
                ->andWhereNotIn('p.id', $ids)
                ->orderBy('p.created_at desc');
        $result = $q2->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
        if(($result['ids'])) $ids = array_merge($ids, explode(',', $result['ids']));
        
        //busqueda en titulo con or en el where
        $serchTerm = str_replace('colum', 'p.titulo', $orLike);
        $q3 = Doctrine_Query::create()
                ->select("group_concat(p.id separator ',') as ids")
                ->from('Publicacion p')
                ->where('p.titulo '.$serchTerm)
                ->andWhere('p.id_estado = ?', Estado::VISIBLE)
                ->andWhereNotIn('p.id', $ids)
                ->orderBy('p.created_at desc');
        $result = $q3->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
        if(($result['ids'])) $ids = array_merge($ids, explode(',', $result['ids']));
        
        //busqueda en contenido con or en el where
        $serchTerm = str_replace('colum', 'p.contenido', $orLike);
        $q4 = Doctrine_Query::create()
                ->select("group_concat(p.id separator ',') as ids")
                ->from('Publicacion p')
                ->where('p.contenido '.$serchTerm)
                ->andWhere('p.id_estado = ?', Estado::VISIBLE)
                ->andWhereNotIn('p.id', $ids)
                ->orderBy('p.created_at desc');
        $result = $q4->fetchOne(array(), Doctrine::HYDRATE_ARRAY);
        if(($result['ids'])) $ids = array_merge($ids, explode(',', $result['ids']));
        return array_unique($ids);
    }
    
}
?>